const dayjs = require('dayjs');
const Database = require('better-sqlite3');

const SqlBuilder = require('./SqlBuilder');

class SQLiteHelper {
  db = null;

  constructor(dbFile) {
    this.db = new Database(dbFile);
  }

  databases () {
    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true,
        datas: [{ Name: 'SQLite' }]
      };

      try {
        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  }

  /**
   * 执行无返回结果集的查询(添加、更新、删除)。
   * @param {String} sql sql语句
   * @param {any} values 参数值
   * @returns {Promise} 异步操作对象
   */
  execute (sql, values) {
    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true
      };

      try {
        if (values && Array.isArray(values)) {
          let stmt = this.db.prepare(sql);
          let execMany = this.db.transaction(items => {
            for (let item of items) {
              stmt.run(item);
            }
          });

          execMany(values);
        } else {
          if (sql.indexOf(';') == -1) {
            let stmt = this.db.prepare(sql);

            values ? stmt.run(values) : stmt.run();
          } else {
            let sqlItems = sql.split(';').filter(s => s.trim().length > 0);

            sqlItems.forEach(s => {
              let stmt = this.db.prepare(s);

              values ? stmt.run(values) : stmt.run();
            });
          }
        }

        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  }

  /**
   * 查询单条数据。
   * @param {String} sql 查询语句
   * @param {Array} values 参数值
   * @param {Function | Number} callback 动态参数设置回调函数或数据库服务器标识(number型)
   * @returns {Promise} 异步操作对象
   */
  queryForObject (sql, values, callback) {
    let tempSql = sql;
    let builder = new SqlBuilder(values, 'sqlite');

    if (typeof callback == 'function' && callback != null) {
      callback(builder);
    }

    tempSql += builder.build();

    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true
      };

      try {
        let stmt = this.db.prepare(tempSql);
        let data = stmt.get(builder.getValues());

        if (data) {
          rs.data = data;
        }

        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  }

  /**
   * 查询多条数据。
   * @param {String} sql 查询语句
   * @param {Array} values 参数值
   * @param {Function | Number | String} callback 动态参数设置回调函数或数据库服务器标识(number型)
   * @returns {Promise} 异步操作对象
   */
  queryForList (sql, values, callback = null) {
    let tempSql = sql;
    let builder = new SqlBuilder(values, 'sqlite');

    if (typeof callback == 'function' && callback != null) {
      callback(builder);
    }

    tempSql += builder.build();

    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true
      };

      try {
        let stmt = this.db.prepare(tempSql);

        rs.datas = stmt.all(builder.getValues());

        if (builder.enablePaged) {
          let totalStmt = this.db.prepare(builder.getTotalSql(tempSql));

          rs.total = (totalStmt.get(builder.getValues()) || {}).totals || rs.datas.length;
        }

        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  };

  tables (tableName) {
    let sql = 'SELECT * from sqlite_master';

    if (tableName && tableName != 'SQLite') {
      sql += ` where name='${tableName}'`;
    }

    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true
      };

      try {
        let stmt = this.db.prepare(sql);

        let datas = stmt.all();

        (datas || []).forEach(d => d.table_name = d.name);

        rs.datas = datas;

        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  }

  columns (dbName, tableName) {
    tableName = tableName;

    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true
      };

      try {
        let stmt = this.db.prepare(`pragma table_info(${tableName})`);

        let columns = stmt.all();

        rs.datas = (columns || []).map(c => {
          return {
            tableName: tableName,
            column_name: c.name,
            column_comment: c.name,
            column_type: c.type,
            is_nullable: c.notnull ? 'NO' : 'YES',
            column_default: c.dflt_value,
            column_key: c.pk ? 'PRI' : ''
          };
        });

        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  }

  tableDatas (dbName, tableName, so) {
    so = so || {};
    so.pageIndex = so.pageIndex || 1;
    so.pageSize = so.pageSize || 20;

    let builder = new SqlBuilder(so, 'sqlite');
    let tempSql = `select * from ${tableName}` + builder.build();

    return new Promise((resolve, reject) => {
      let rs = {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: true
      };

      try {
        let stmtColumns = this.db.prepare(`pragma table_info(${tableName})`);

        let columns = stmtColumns.all();

        rs.columns = (columns || []).map(c => {
          return {
            field: c.name,
            title: c.name,
          };
        });

        let stmt = this.db.prepare(tempSql);
        let totalStmt = this.db.prepare(builder.getTotalSql(tempSql));

        rs.datas = stmt.all(builder.getValues());
        rs.total = (totalStmt.get(builder.getValues()) || {}).totals || rs.datas.length;

        resolve(rs);
      } catch (err) {
        reject(err);
      }
    });
  }

  async tableDDL (dbName, tableName) {
    let rs = await this.tables(tableName);

    rs.data = {
      Table: rs.datas[0].name,
      'Create Table': rs.datas[0].sql
    };

    delete rs.datas;

    return rs;
  }

  async tableDML (dbName, tableName) {
    let rs = await this.columns(dbName, tableName);

    let dmls = [];

    let querySql = '';
    let orders = '';
    let insertColumns = '';
    let insertValues = '';
    let updateColumns = '';
    let whereColumns = '';

    rs.datas.forEach(column => {
      let seperator = column != rs.datas[rs.datas.length - 1] ? ',\n  ' : '';

      querySql += `\`${column.column_name}\`${seperator}`;

      if (!column.IsIdentity) {
        insertColumns += `\`${column.column_name}\`${seperator}`;
        insertValues += `<${column.column_name}:${column.column_type}>${seperator}`;
      }

      if (!column.IsIdentity && column.column_key != 'PRI') {
        updateColumns += `\`${column.column_name}\`=<${column.column_name}:${column.column_type}>${seperator}`;
      }

      if (column.column_key) {
        orders += (orders.length > 0 ? ', ' : '');
        orders += `[${column.column_name}]`;

        whereColumns += (whereColumns.length > 0 ? ' AND' : '');
        whereColumns += `\`${column.column_name}\`=<${column.column_name}:${column.column_type}>`;
      }
    });

    dmls.push('--查询语句');
    dmls.push(`SELECT\n  ${querySql} \nFROM \`${tableName}\`\nWHERE ${whereColumns};`);

    dmls.push('--分页查询语句');
    dmls.push(`SELECT\n  ${querySql} \nFROM \`${tableName}\`\nORDER BY ${orders}\nLIMIT 0, 20;`);

    dmls.push('--添加数据');
    dmls.push(`INSERT INTO \`${tableName}\`\n(\n  ${insertColumns}\n)\nVALUES\n(\n  ${insertValues}\n);`);

    dmls.push('--更新数据');
    dmls.push(`UPDATE \`${tableName}\`\nSET ${updateColumns}\nWHERE ${whereColumns};`);

    dmls.push('--删除数据');
    dmls.push(`DELETE FROM \`${tableName}\` WHERE ${whereColumns};`);

    return {
      at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
      success: true,
      datas: dmls
    };
  }
}

module.exports = SQLiteHelper;